package my.dao.lmpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.sun.org.apache.regexp.internal.recompile;

import my.bean.Tea;
import my.dao.ITeaDao;
import my.dbUtil.DBUtils;
import oracle.net.aso.l;
import sun.security.pkcs11.Secmod.DbMode;

public class ITeaDaoImpl implements ITeaDao {

	private Connection conn;

	public ITeaDaoImpl() {
	}

	public ITeaDaoImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public int addTea(Tea tea) throws SQLException {
		PreparedStatement prestmt = null;
		int record = 0;

		try {
			String sql = "insert into tea values (tea_id.nextval,?,?,?,?,?,?)";
			prestmt = conn.prepareStatement(sql);
			prestmt.setString(1, tea.getName());
			prestmt.setString(2, tea.getFrom());
			prestmt.setDouble(3, tea.getPrice());
			prestmt.setDouble(4, tea.getStock());
			prestmt.setString(5, tea.getIntroduction());
			prestmt.setInt(6, tea.getType());

			record = prestmt.executeUpdate();

			return record;
		} catch (SQLException e) {
			System.err.println("插入失败!");
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}

	}

	@Override
	public void addTeaList(List<Tea> list) throws SQLException {
		PreparedStatement prestmt = null;

		try {
			String sql = "insert into tea values (tea_id.nextval,?,?,?,?,?,?)";
			prestmt = conn.prepareStatement(sql);
			for (Tea tea : list) {
				prestmt.setString(1, tea.getName());
				prestmt.setString(2, tea.getFrom());
				prestmt.setDouble(3, tea.getPrice());
				prestmt.setDouble(4, tea.getStock());
				prestmt.setString(5, tea.getIntroduction());
				prestmt.setInt(6, tea.getType());
				prestmt.addBatch();
			}

			prestmt.executeBatch();
		} catch (SQLException e) {
			System.err.println("批量插入失败!");
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}

	}

	@Override
	public int deleteTeaById(int id) throws SQLException {
		PreparedStatement prestmt = null;
		int record = 0;
		try {
			String sql = "delete from tea where tea_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, id);

			record = prestmt.executeUpdate();

			return record;
		} catch (SQLException e) {
			System.err.println("删除失败!");
			e.printStackTrace();
			throw e;
		}
	}

	@Override
	public int modifyTeaMessage(Tea tea) throws SQLException {
		PreparedStatement prestmt = null;
		int record = 0;

		try {
			String sql = "update tea set tea_name=?,tea_from=?,tea_price=?,tea_stock=?,tea_introduction=?,tea_type=? where tea_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setString(1, tea.getName());
			prestmt.setString(2, tea.getFrom());
			prestmt.setDouble(3, tea.getPrice());
			prestmt.setDouble(4, tea.getStock());
			prestmt.setString(5, tea.getIntroduction());
			prestmt.setInt(6, tea.getType());
			prestmt.setInt(7, tea.getId());

			record = prestmt.executeUpdate();

			return record;
		} catch (SQLException e) {
			System.err.println("修改失败!");
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}
	}

	@Override
	public List<Tea> findAll() {
		List<Tea> list = new ArrayList<>();
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from tea";
			prestmt = conn.prepareStatement(sql);

			rs = prestmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				String from = rs.getString(3);
				double price = rs.getDouble(4);
				double stock = rs.getDouble(5);
				String introduction = rs.getString(6);
				int type = rs.getInt(7);

				Tea tea = new Tea(id, name, from, price, stock, type, introduction);
				list.add(tea);
			}

			return list;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public List<Tea> findTeaByName(String name) {
		List<Tea> list = new ArrayList<>();
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from tea where tea_name like ?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setString(1, "%" + name + "%");

			rs = prestmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt(1);
				name = rs.getString(2);
				String from = rs.getString(3);
				double price = rs.getDouble(4);
				double stock = rs.getDouble(5);
				String introduction = rs.getString(6);
				int type = rs.getInt(7);

				Tea tea = new Tea(id, name, from, price, stock, type, introduction);
				list.add(tea);
			}

			return list;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public List<Tea> findTeaByType(int type) {
		List<Tea> list = new ArrayList<>();
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from tea where tea_type=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, type);

			rs = prestmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				String from = rs.getString(3);
				double price = rs.getDouble(4);
				double stock = rs.getDouble(5);
				String introduction = rs.getString(6);

				Tea tea = new Tea(id, name, from, price, stock, type, introduction);
				list.add(tea);
			}

			return list;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public Tea findTeaById(int id) {
		Tea tea = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from tea where tea_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, id);

			rs = prestmt.executeQuery();

			while (rs.next()) {
				String name = rs.getString(2);
				String from = rs.getString(3);
				double price = rs.getDouble(4);
				double stock = rs.getDouble(5);
				String introduction = rs.getString(6);
				int type = rs.getInt(7);

				tea = new Tea(id, name, from, price, stock, type, introduction);
			}

			return tea;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return tea;
	}

}
